package Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 相关DB操作类
 */
public class DBService {

    private static String sql = null;// SQL语句
    private static DBConnection dbcon = null;// DBConnection对象
    private static ResultSet rs = null;// 执行语句后得到的结果集

    /**
     * 查询所有Email数据
     * @return 存储Email数据的List
     */
    public static List<String> getEmails(){

        sql = "select Email from useremail";
        dbcon =new DBConnection(sql);
        List<String> result = new ArrayList<String>();

        try{
            rs = dbcon.pst.executeQuery();
            while(rs.next()){
                result.add(rs.getString("Email"));
            }
            rs.close();
            dbcon.close();
        }catch(SQLException e){
            e.printStackTrace();
        }

        return result;

    }

    /**
     * 查询所有Name数据
     * @return 存储Name数据的List
     */
    public static List<String> getNames(){

        sql = "select Name from useremail";
        dbcon =new DBConnection(sql);
        List<String> result = new ArrayList<String>();

        try{
            rs = dbcon.pst.executeQuery();
            while(rs.next()){
                result.add(rs.getString("Name"));
            }
            rs.close();
            dbcon.close();
        }catch(SQLException e){
            e.printStackTrace();
        }

        return result;

    }

    /**
     * 根据Email数据查询对应的Name
     * @param email 待查询的Email数据
     * @return 对应Name的值
     */
    public static String getNameByEmail(String email) {

        sql = "select Name from useremail where Email = " + "'" + email + "'";
        dbcon = new DBConnection(sql);
        String result = null;

        try {
            rs = dbcon.pst.executeQuery();
            if (rs.next()) {
                result = rs.getString("Name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;

    }

}
